﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using Aulac.Srv.Db.Provider; using Aulac.Srv.Db.Connect;
using Aulac.Rsc.Ems; using Aulac.Rsc.Ems.Tags;
using Aulac.Global.Enums; using Aulac.Global.Data; using Aulac.Global.Common; using Aulac.Global.Xml;
using Aulac.Srv.Db.Common;
using System.Data;
using System.Data.Common;

namespace Aulac.Srv.Db.Data.Ems.MdcFiles.Pnts
{
    partial class PntInfoPrvd
    {
        /* Code Date: 02/17/2011 by Thangnc */

 
        /// <summary>
        /// Set thong tin vao Table PntInfo
        /// </summary>
        private bool SetPntInfoDb(XmlDataDocument xmlDoc)
        {
            XmlElement eHeader = Aulac.Global.Xml.XmlProcess.GetVchHeaderElm(xmlDoc);
            string sPntPrkID = XmlProcess.GetValueAtrb(eHeader, tagMdcFiles.Pnt.PntPrkID);
            if (ValueHelper.isEmpty(sPntPrkID))
                 AddPntInfoDb(eHeader);
            else
                 UpdatePntInfoDb(eHeader);
            return true;
        }
        
 
        private XmlDataDocument FindPntToRegExamDb(XmlDataDocument xmlParam)
        {
            List<string> listPnt = new List<string>(new string[] { tagMdcFiles.Pnt.PntPrkID, tagMdcFiles.Pnt.PntID, tagMdcFiles.Pnt.PntName, tagMdcFiles.Pnt.PntBirthday, tagMdcFiles.Pnt.PntBirthYear, tagMdcFiles.Pnt.PntAddr, tagMdcFiles.Pnt.PntRacePrkID, tagMdcFiles.Pnt.RaceID, tagMdcFiles.Pnt.RaceName, tagMdcFiles.Pnt.PntSexID, tagMdcFiles.Pnt.SexName, tagMdcFiles.Pnt.PntTownWardPrkID, tagMdcFiles.Pnt.PntDistrictPrkID, tagMdcFiles.Pnt.PntProvincePrkID, tagMdcFiles.Pnt.PntCountryPrkID, tagMdcFiles.Pnt.PntJobGrpPrkID, tagMdcFiles.Pnt.PntPlaceWork, tagMdcFiles.Pnt.PntPhone, tagMdcFiles.Pnt.InsNum, tagMdcFiles.Pnt.InsDate, tagMdcFiles.Pnt.InsExpireDate, tagMdcFiles.Pnt.IsActive, tagMdcFiles.Pnt.PntCountryID, tagMdcFiles.Pnt.PntCountryName, tagMdcFiles.Pnt.PntDistrictID, tagMdcFiles.Pnt.PntDistrictName, tagMdcFiles.Pnt.PntProvinceName, tagMdcFiles.Pnt.PntProvinceID });

            string sPntName = XmlProcess.GetValueParam(xmlParam, tagMdcFiles.Pnt.PntName);
            string sPntID = XmlProcess.GetValueParam(xmlParam, tagMdcFiles.Pnt.PntID);
            string sInsNum = XmlProcess.GetValueParam(xmlParam, tagMdcFiles.Pnt.InsNum);
            string sInsNumFormat = XmlProcess.GetValueParam(xmlParam, tagMdcFiles.Pnt.InsNumFormat);
            string sCountryPrkID = XmlProcess.GetValueParam(xmlParam, tagMdcFiles.Pnt.PntCountryPrkID);
            //string sCountryID = XmlProcess.GetValueParam(xmlParam, tagMdcFiles.Pnt.PntCountryPrkID);

            string sProvincePrkID = XmlProcess.GetValueParam(xmlParam, tagMdcFiles.Pnt.PntProvincePrkID);
            string sDistrictPrkID = XmlProcess.GetValueParam(xmlParam, tagMdcFiles.Pnt.PntDistrictPrkID);
            string sTownWardPrkID = XmlProcess.GetValueParam(xmlParam, tagMdcFiles.Pnt.PntTownWardPrkID);

            string sFilterInsNum = string.Format(" (? IS NULL OR {0} =?)", tagMdcFiles.Pnt.InsNum);

            string sFilterPntID = string.Format(" AND (? IS NULL OR {0} =?)", tagMdcFiles.Pnt.PntID);
            

            string sFilterPntName = string.Format(" AND (? IS NULL OR {0} = ?)", tagMdcFiles.Pnt.PntName);
            string sFilterBirth = string.Format(" AND (? IS NULL OR {0} = ?)", tagMdcFiles.Pnt.PntBirthYear);
            string sFilterCounttry = string.Format(" AND (? IS NULL OR {0} = ?)", tagMdcFiles.Pnt.PntCountryPrkID);
            string sFilterPrvc = string.Format(" AND (? IS NULL OR {0} = ?)",tagMdcFiles.Pnt.PntProvincePrkID);
            string sFilterDistrict = string.Format(" AND (? IS NULL OR {0} = ?)", tagMdcFiles.Pnt.PntDistrictPrkID);
            string sFilterTownward = string.Format(" AND (? IS NULL OR {0} = ?)", tagMdcFiles.Pnt.PntTownWardPrkID);

            sqlQuery = CreateQueryStringFindPntToReg();

            string sFilter = string.Format("{0}", sFilterInsNum);
            sFilter += string.Format("{0}", sFilterPntID);
            sFilter += string.Format("{0}", sFilterPntName);
            sFilter += string.Format("{0}", sFilterCounttry);
            sFilter += string.Format("{0}", sFilterPrvc);
            sFilter += string.Format("{0}", sFilterDistrict);
            sFilter += string.Format("{0}", sFilterTownward);

            string query = string.Format("{0} WHERE {1}",sqlQuery , sFilter);

            DataTable tbResult = mDbPrd.ExecuteReaderDataToTable(mDbCmd, query, sInsNumFormat, sInsNumFormat, sPntID, sPntID, sPntName, sPntName, sCountryPrkID, sCountryPrkID, sProvincePrkID, sProvincePrkID, sDistrictPrkID, sDistrictPrkID, sTownWardPrkID, sTownWardPrkID);
            if(ValueHelper.isEmpty(tbResult))
            {
                sFilter = sFilter.Replace(sFilterTownward, "");
                query = string.Format("{0} WHERE {1}", sqlQuery, sFilter);
                tbResult = mDbPrd.ExecuteReaderDataToTable(mDbCmd, query, sInsNumFormat, sInsNumFormat, sPntID, sPntID, sPntName, sPntName, sCountryPrkID, sCountryPrkID, sProvincePrkID, sProvincePrkID, sDistrictPrkID, sDistrictPrkID);
            }
            if (ValueHelper.isEmpty(tbResult))
            {
                sFilter = sFilter.Replace(sFilterDistrict, "");
                query = string.Format("{0} WHERE {1}", sqlQuery, sFilter);

                tbResult = mDbPrd.ExecuteReaderDataToTable(mDbCmd, query, sInsNumFormat, sInsNumFormat, sPntID, sPntID, sPntName, sPntName, sCountryPrkID, sCountryPrkID, sProvincePrkID, sProvincePrkID);
                
            }
            if (ValueHelper.isEmpty(tbResult))
            {
                sFilter = sFilter.Replace(sFilterPrvc, "");
                query = string.Format("{0} WHERE {1}", sqlQuery, sFilter);

                tbResult = mDbPrd.ExecuteReaderDataToTable(mDbCmd, query, sInsNumFormat, sInsNumFormat, sPntID, sPntID, sPntName, sPntName, sCountryPrkID, sCountryPrkID);
            }
            if (ValueHelper.isEmpty(tbResult))
            {
                sFilter = sFilter.Replace(sFilterCounttry, "");
                query = string.Format("{0} WHERE {1}", sqlQuery, sFilter);

                tbResult = mDbPrd.ExecuteReaderDataToTable(mDbCmd, query, sInsNumFormat, sInsNumFormat, sPntID, sPntID, sPntName, sPntName);
            }
            if (!ValueHelper.isEmpty(tbResult))
            {
                XmlDataDocument xmlOut = mDbPrd.CreateXmlDocument("", "");
                XmlElement eLines = XmlProcess.AddVchLinesElm(ref xmlOut);
                DataTableProcess.GetValuesToChildElmXml(tbResult, mDbPrd.DbAulacConnection.CultureInfo, ref eLines);
                return xmlOut;
            }
            else
            {
                PntInses.PntInsPrvd pntIns = new Aulac.Srv.Db.Data.Ems.MdcFiles.PntInses.PntInsPrvd(mDbPrd);
                return pntIns.FindPntToRegExam(sPntName, sInsNum);
            }

        }

        private XmlDataDocument AddPntInfoDb(XmlElement ePntInfo)
        {
            string sRegInsCode = XmlProcess.GetValueAtrb(ePntInfo, tagMdcFiles.PntRdcObj.InsRegExamCodeID);
            string[] arr = sRegInsCode.Split("-".ToCharArray());
            if (ValueHelper.isEmpty(arr) && arr.Length == 2)
            {
                XmlProcess.SetAtrb(ref ePntInfo, tagMdcFiles.Pnt.HspInsID, arr[1]);
                XmlProcess.SetAtrb(ref ePntInfo, tagMdcFiles.Pnt.PrvcInsID, arr[0]);
            }

            Aulac.Srv.Data.XmlQuery.SrvExecXmlQueryData query = new Aulac.Srv.Data.XmlQuery.SrvExecXmlQueryData(mDbPrd);
            XmlElement eHeader = query.SetVchHeaderElm(QueryCmdTypes.AddDataTable);
            query.SetTableName(eHeader, TableNames.MdcFile_PntInfo);
            query.ObjQueryVchHeader.AddFieldPrk(tagMdcFiles.Pnt.PntPrkID, System.Data.DbType.Decimal, KeyTypes.AutoNumber);
            query.ObjQueryVchHeader.AddFieldResultItem(tagMdcFiles.Pnt.PntPrkID, System.Data.DbType.Decimal);
            query.ImportData(query.ObjQueryVchHeader, ref eHeader, ePntInfo);
            XmlDataDocument xmlDoc = query.ExecureQuery();
            
            XmlProcess.SetAtrb(ref ePntInfo, tagMdcFiles.Pnt.PntPrkID, XmlProcess.GetValueVchHeader(xmlDoc, tagMdcFiles.Pnt.PntPrkID));
            return xmlDoc;
            
            /*string[] arr = mList.GetRange(1, mList.Count - 1).ToArray();

            sqlQuery = SqlHelper.CreateSqlInsert(Aulac.Resource.Ems.TableNames.MdcFilesPntInfo, arr);
            DbDataProvider.AddParamToCmd(mDbCmd, ePntInfo, arr);
            return DbDataProvider.ExecuteNonQuery(ref mDbCmd, sqlQuery) >0;
             */ 
        }
        private XmlDataDocument UpdatePntInfoDb(XmlElement ePntInfo)
        {
            /* Code Update: 02/16/2011 by H.Rin */
            string sRegInsCode = XmlProcess.GetValueAtrb(ePntInfo, tagMdcFiles.PntRdcObj.InsRegExamCodeID);
            string[] arr = sRegInsCode.Split("-".ToCharArray());
            if (ValueHelper.isEmpty(arr) && arr.Length == 2)
            {
                XmlProcess.SetAtrb(ref ePntInfo, tagMdcFiles.Pnt.HspInsID, arr[1]);
                XmlProcess.SetAtrb(ref ePntInfo, tagMdcFiles.Pnt.PrvcInsID, arr[0]);
            }

            Aulac.Srv.Data.XmlQuery.SrvExecXmlQueryData query = new Aulac.Srv.Data.XmlQuery.SrvExecXmlQueryData(mDbPrd);
            
            XmlElement eHeader = query.SetVchHeaderElm(QueryCmdTypes.UpdateDataTable);
            query.SetTableName(eHeader, TableNames.MdcFile_PntInfo);
            query.ObjQueryVchHeader.AddFieldPrk(tagMdcFiles.Pnt.PntPrkID, System.Data.DbType.Decimal, KeyTypes.AutoNumber, RefValueTypes.InnerHeaderElm);
            query.ObjQueryVchHeader.AddFieldResultItem(tagMdcFiles.Pnt.PntPrkID, System.Data.DbType.Decimal);
            query.ImportData(query.ObjQueryVchHeader, ref eHeader, ePntInfo);
            query.ObjQueryVchHeader.AddValueParamFilterItem(tagMdcFiles.Pnt.PntPrkID, tagMdcFiles.Pnt.PntPrkID, XmlProcess.GetValueVchHeader(query.XmlQueryDoc, tagMdcFiles.Pnt.PntPrkID));

            return query.ExecureQuery();            
        }
        private XmlDataDocument UpdatePntInfoDb(XmlDataDocument xmlData)
        {
            /* Author Write: H.Rin */
            /* Code Update: 02/16/2011 */

            XmlElement ePntInfo = Aulac.Global.Xml.XmlProcess.GetVchHeaderElm(xmlData);
            string sPntPrkID = XmlProcess.GetValueVchHeader(xmlData, tagMdcFiles.Pnt.PntPrkID);
            Aulac.Srv.Data.XmlQuery.SrvExecXmlQueryData query = new Aulac.Srv.Data.XmlQuery.SrvExecXmlQueryData(mDbPrd);
            XmlElement eHeader = query.SetVchHeaderElm();
            query.ObjQueryVchHeader.QueryCmdType = QueryCmdTypes.UpdateDataTable;
            query.ObjQueryVchHeader.TableName = TableNames.MdcFile_PntInfo;
            query.ObjQueryVchHeader.AddFieldPrk(tagMdcFiles.Pnt.PntPrkID, System.Data.DbType.Decimal, KeyTypes.AutoNumber);
            query.ObjQueryVchHeader.AddFieldResultItem(tagMdcFiles.Pnt.PntPrkID, System.Data.DbType.Decimal);
            query.ImportData(query.ObjQueryVchHeader, ref eHeader, ePntInfo);
            query.ObjQueryVchHeader.AddValueParamFilterItem(tagMdcFiles.MdcFiles.PntPrkID, tagMdcFiles.MdcFiles.PntPrkID, sPntPrkID);

            return query.ExecureQuery();
        }
        private string CreateQueryStringFindPntToReg()
        {
            string[] arrPnt = new string[] { tagMdcFiles.Pnt.PntPrkID, tagMdcFiles.Pnt.PntID, tagMdcFiles.Pnt.PntName, tagMdcFiles.Pnt.PntBirthday, tagMdcFiles.Pnt.PntBirthYear, tagMdcFiles.Pnt.PntAddr, tagMdcFiles.Pnt.PntRacePrkID, tagMdcFiles.Pnt.PntSexID, tagMdcFiles.Pnt.PntTownWardPrkID, tagMdcFiles.Pnt.PntDistrictPrkID, tagMdcFiles.Pnt.PntProvincePrkID, tagMdcFiles.Pnt.PntCountryPrkID, tagMdcFiles.Pnt.PntJobGrpPrkID, tagMdcFiles.Pnt.PntPlaceWork, tagMdcFiles.Pnt.PntPhone, tagMdcFiles.Pnt.InsNum, tagMdcFiles.Pnt.InsDate, tagMdcFiles.Pnt.InsExpireDate, tagMdcFiles.Pnt.PrvcInsID, tagMdcFiles.Pnt.HspInsID };
            
            string[] arrCntr = new string[] {string.Format("{0} as {1}", tagItems.Countries.CountryID, tagMdcFiles.Pnt.PntCountryID), string.Format("{0} as {1}", tagItems.Countries.CountryName, tagMdcFiles.Pnt.PntCountryName) };
            string[] arrPrvc = new string[] {string.Format("{0} as {1}", tagItems.Province.ProvinceID, tagMdcFiles.Pnt.PntProvinceID), string.Format("{0} as {1}", tagItems.Province.ProvinceName, tagMdcFiles.Pnt.PntProvinceName)};
            string[] arrDistric = new string[] {string.Format("{0} as {1}", tagItems.District.DistrictID, tagMdcFiles.Pnt.PntDistrictID), string.Format("{0} as {1}", tagItems.District.DistrictName, tagMdcFiles.Pnt.PntDistrictName) };
            string[] arrTown = new string[] { string.Format("{0} as {1}",tagItems.TownWard.TownWardID, tagMdcFiles.Pnt.PntTownWardID), string.Format("{0} as {1}",tagItems.TownWard.TownWardName, tagMdcFiles.Pnt.PntTownWardName)};
            string[] arrPntSex = new string[] { string.Format("{0} as {1}", tagItems.Sex.SexName, tagMdcFiles.Pnt.PntSexName)};
            string[] arrPntRace = new string[] {string.Format("{0} as {1} ", tagItems.Race.RaceID, tagMdcFiles.Pnt.PntRaceID), string.Format("{0} as {1}", tagItems.Race.RaceName, tagMdcFiles.Pnt.PntRaceName) };

            sqlQuery = string.Format("SELECT DISTINCT pnt.{0}, cntr.{1}, prvc.{2}, distr.{3}, town.{4}, pntSex.{5},pntRace.{6} ",
                StringHelper.ConvertArrayToString(", pnt.", arrPnt), StringHelper.ConvertArrayToString(", cntr.", arrCntr),
                StringHelper.ConvertArrayToString(", prvc.", arrPrvc), StringHelper.ConvertArrayToString(", distr.", arrDistric),
                StringHelper.ConvertArrayToString(", town.", arrTown), StringHelper.ConvertArrayToString(", pntSex.", arrPntSex),
                StringHelper.ConvertArrayToString(", pntRace.", arrPntRace));

            sqlQuery += string.Format(" FROM {0} pnt INNER JOIN {1} cntr", TableNames.MdcFile_PntInfo, TableNames.Dm_Countries);
            sqlQuery += string.Format(" ON pnt.{0} = cntr.{1}", tagMdcFiles.Pnt.PntCountryPrkID, tagItems.Countries.CountryPrkID);

            sqlQuery += string.Format(" INNER JOIN {0} prvc ", TableNames.Dm_Province);
            sqlQuery += string.Format(" ON pnt.{0} = prvc.{1}", tagMdcFiles.Pnt.PntProvincePrkID, tagItems.Province.ProvincePrkID);

            sqlQuery += string.Format(" INNER JOIN {0} distr ", TableNames.Dm_District);
            sqlQuery += string.Format(" ON pnt.{0} = distr.{1}", tagMdcFiles.Pnt.PntDistrictPrkID, tagItems.District.DistrictPrkID);
            
            sqlQuery += string.Format(" INNER JOIN {0} town ", TableNames.Dm_TownWard);
            sqlQuery += string.Format(" ON pnt.{0} = town.{1}", tagMdcFiles.Pnt.PntTownWardPrkID, tagItems.TownWard.TownWardPrkID);
            
            sqlQuery += string.Format(" INNER JOIN {0} pntSex ", TableNames.Dm_SexTypes);
            sqlQuery += string.Format(" ON pnt.{0} = pntSex.{1}", tagMdcFiles.Pnt.PntSexID, tagItems.Sex.SexID);
            
            sqlQuery += string.Format(" INNER JOIN {0} pntRace ", TableNames.Dm_Race);
            sqlQuery += string.Format(" ON pnt.{0} = pntRace.{1}", tagMdcFiles.Pnt.PntRacePrkID, tagItems.Race.RacePrkID);
            
            return sqlQuery;
        }

        /// <summary>
        /// Xoa PntInfo trong MdcFile_PntInfo
        /// </summary>
        /// <param name="pPntPrkID"></param>
        /// <returns></returns>
        private bool DelPntInfoDb(string pPntPrkID)
        {
            sqlQuery = SqlHelper.CreateSqlDelete(TableNames.MdcFile_PntInfo, false, tagMdcFiles.Pnt.PntPrkID);
            return mDbPrd.ExecuteNonQuery(ref mDbCmd,sqlQuery, pPntPrkID) >= 0;
        }
    }
}
